package com.briup.estore.common.utils;

import com.briup.estore.common.constant.ExceptionMsgEnum;
import com.briup.estore.common.constant.SystemExceptionConstant;
import com.briup.estore.common.exception.EstoreException;
import com.briup.estore.common.exception.EstoreSystemException;
import com.briup.estore.common.pojo.User;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.apache.commons.logging.impl.Log4JLogger;
import org.apache.log4j.Logger;

import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * @Author briup-adam
 * @Date 2023/8/1 上午10:52
 * @Description JDBC工具类
 **/

public class JDBCUtils {
    private static Logger log= LogUtils.log();

    //使用DBCP2来配置文件
    private static DataSource ds;
    private static Properties properties;

    static {
        try {
            properties = new Properties();
            String fileName = "dbcp2.properties";
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream(fileName);
            properties.load(is);
            ds = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 获取数据库连接对象
     *
     * @return 数据库连接对象
     */
    private static Connection getConnection() {
        Connection connection = null;
        try {
            connection = ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 查询单条数据
     *
     * @param sql         要执行的sql
     * @param targetClass 映射的类型
     * @param params      替换占位符的参数
     * @param <R>         映射的类型
     * @return 查找到的映射对象 或者为null
     */
    public static <R> R findOne(String sql, Class<R> targetClass, Object... params)   {
        log.debug(sql);
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        R result = null;
//            创建 statement对象
        try {
            ps = conn.prepareStatement(sql);
            //            设置参数
            ps = setParams(ps, params);
//            执行sql
            rs = ps.executeQuery();
//            处理结果集
            List<R> list = getResult(targetClass, rs);
            if (list.size() > 1) {
                throw new EstoreSystemException(SystemExceptionConstant.EXCESS_ELEMENT);
            } else if (list.size() < 1) {
                result = null;
            } else {
                result = list.get(0);
            }
            rs.close();
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw  new  EstoreException(ExceptionMsgEnum.ERROR);
        }
        return result;
    }

    /**
     * 查询多条数据
     *
     * @param sql         要执行的sql
     * @param targetClass 将每一行数据映射成类型
     * @param params      替换占位符的参数
     * @param <R>         要映射的类型
     * @return 返回目标类型数据的列表
     */
    public static <R> List<R> findMany(String sql, Class<R> targetClass, Object... params) {
        log.debug(sql);
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<R> list = null;
//            创建 statement对象
        try {
            ps = conn.prepareStatement(sql);
//            设置参数
            ps = setParams(ps, params);
//            执行sql
            rs = ps.executeQuery();
//            处理结果集
            list = getResult(targetClass, rs);
            rs.close();
            ps.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
            throw new EstoreException(ExceptionMsgEnum.ERROR);
        }
        return list;
    }

    /**
     * 执行 insert  、 update 、delete 语句用到的方法
     *
     * @param sql    要执行的sql语句
     * @param params sql中要替换占位的参数
     * @return 受影响的行数
     */
    public static int DML(String sql, Object... params)  {
        log.debug(sql);
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int rows = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps = setParams(ps, params);
            rows = ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw  new   EstoreException(ExceptionMsgEnum.ERROR);
        }

        return rows;

    }

    /**
     * 替换PreparedStatement 中的占位符 使用传入的可变长参数列表
     *
     * @param ps     构建的statement对象爱你改
     * @param params sql语句中要替换占位符的参数
     * @return 替换完占位符的statement对象
     * @throws SQLException
     * @throws NoSuchMethodException
     * @throws EstoreSystemException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    private static PreparedStatement setParams(PreparedStatement ps, Object[] params) throws SQLException, NoSuchMethodException, EstoreSystemException, InvocationTargetException, IllegalAccessException {
        for (int i = 0; i < params.length; i++) {
            Class<? extends PreparedStatement> psClass = ps.getClass();
            Method m = null;
//            获取参数的类型名称
            String name = params[i].getClass().getSimpleName();

            switch (name) {
                case "Integer":
                    m = psClass.getMethod("setInt", int.class, int.class);
                    break;
                case "Double":
                    m = psClass.getMethod("setDouble", int.class, double.class);
                    break;
                case "Boolean":
                    m = psClass.getMethod("setBoolean", int.class, boolean.class);
                    break;
                case "String":
                    m = psClass.getMethod("setString", int.class, String.class);
                    break;
                case "Timestamp":
                    m = psClass.getMethod("setTimestamp", int.class, Timestamp.class);
                    break;
                default:
                    throw new EstoreSystemException(SystemExceptionConstant.TYPE_NOT_SUPPORTED);

            }
            m.invoke(ps, i + 1, params[i]);
        }
        return ps;
    }

    /**
     * 结果集处理
     *
     * @param targetClass 要将结果集映射成的类型
     * @param rs          结果集
     * @param <R>         要将结果集映射成的类型
     * @return 结果集映射
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws EstoreSystemException
     * @throws NoSuchMethodException
     * @throws SQLException
     * @throws NoSuchFieldException
     * @throws InvocationTargetException
     */
    private static <R> List<R> getResult(Class<? extends R> targetClass, ResultSet rs) throws IllegalAccessException, InstantiationException, EstoreSystemException, NoSuchMethodException, SQLException, NoSuchFieldException, InvocationTargetException {
        ArrayList<R> list = new ArrayList<>();

//        获取ResultSet类型对象
        Class<? extends ResultSet> rsClass = rs.getClass();
        //获取实体类型的所有属性
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();
//        判断返回的结果集行数是否有更多
        while (rs.next()) {
            //构建目标实体对象
            R instance = targetClass.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                // 获取结果集列名
                String columnName = resultSetMetaData.getColumnName(i);
                String fieldName = "";
                //列名转成属性名的驼峰格式
                if (columnName.contains("_")) {
                    String[] arr = columnName.split("_");
                    fieldName = arr[0];
                    for (int i1 = 1; i1 < arr.length; i1++) {
                        fieldName += CapitalCase(arr[i1]);
                    }

                } else {
                    fieldName = columnName;
                }
                Field field = targetClass.getDeclaredField(fieldName);
                //设置私有属性的可见性
                field.setAccessible(true);
                //获取属性的类型名称
                String typeName = field.getType().getSimpleName();
                String methodName = "get";
                switch (typeName) {
                    case "int":
                    case "Integer":
                        methodName += "Int";
                        break;
                    case "double":
                    case "Double":
                        methodName += "Double";
                        break;
                    case "boolean":
                    case "Boolean":
                        methodName += "Boolean";
                        break;
                    case "String":
                    case "Timestamp":
                        methodName += typeName;
                        break;
                    default:
                        throw new EstoreSystemException(SystemExceptionConstant.TYPE_NOT_SUPPORTED);

                }
                Method m = rsClass.getMethod(methodName, String.class);
                //获取resultSet中的的值
                Object o = m.invoke(rs, columnName);
                //实体属性赋值
                field.set(instance, o);

            }
            list.add(instance);
        }
        return list;
    }

    /**
     * 首字母大写
     *
     * @param s 要改变的字符串
     * @return 首字母大写之后的字符串
     */
    private static String CapitalCase(String s) {
        return s == null ? null : s.length() > 0 ? s.substring(0, 1).toUpperCase() + s.substring(1).toLowerCase() : "";

    }


}
